﻿<cfsilent>
	<cfscript>

		termId = event.getArg("TID");
		depId = event.getArg("DepID");

		academicYear = event.getArg("AcademicYear");
		academicTerm = event.getArg("AcademicTerm");
		
		sql = "	SELECT
					i.institute_name
				FROM
					t_institute i
				WHERE
					i.institute_id = :depId ";
	
		queryObj = new Query(datasource = application.dnsSlave);
		queryObj.addParam(name = "depId", value = depId, cfsqltype = "cf_sql_varchar");
		rs_departmentEntity = queryObj.execute(sql = sql).getResult();
		
		temFile = GetTempDirectory() & createUUID() & ".xls";
		
		downFile = URLEncodedFormat(rs_departmentEntity.institute_name & "-学期教学任务." & academicYear & "-" & academicTerm, "utf-8");
		
		/* 生成电子表格数据 */
		sql = "	SELECT
					at.academic_year,at.academic_term,
					ac.course_name,a.cidx,
					atch.tch_name,atch.tch_id,
					a.task_limite,a.task_chooesd,act.cr_type_name,acu.campus_name,
					a.task_week,a.task_period,
					CASE WHEN a.task_time IS NULL THEN '-' ELSE a.task_time END task_time,
					CASE WHEN a.task_place IS NULL THEN '-' ELSE a.task_place END task_place,
					a.task_prop,
					a.task_classes
				FROM
					t_task a INNER JOIN t_term at ON at.term_id = a.term_id INNER JOIN t_course ac ON ac.cid = a.cid
					INNER JOIN t_teacher atch ON a.tch_id = atch.tch_id INNER JOIN t_classroom_type act ON a.cr_type = act.cr_type
					INNER JOIN t_campus acu ON acu.campus_id = a.campus_id
				WHERE
						at.term_id = :termId 
					AND
						ac.institute_id = :depId 
					AND
						EXISTS(
							SELECT 1 FROM t_task_arrange b WHERE b.task_id = a.tsk_id AND b.term_id = :termId 
						)
				ORDER BY
					ac.course_name,a.cidx";
		
		queryObj = new Query(datasource = application.dnsSlave);
		queryObj.addParam(name = "depId", value = depId, cfsqltype = "cf_sql_varchar");
		queryObj.addParam(name = "termId", value = termId, cfsqltype = "cf_sql_char");
		rs_planTask = queryObj.execute(sql = sql).getResult();

		sql = "	SELECT
					at.academic_year,at.academic_term,
					ac.course_name,a.cidx,
					atch.tch_name,atch.tch_id,
					a.task_limite,a.task_chooesd,act.cr_type_name,acu.campus_name,
					a.task_week,a.task_period,
					CASE WHEN a.task_time IS NULL THEN '-' ELSE a.task_time END task_time,
					CASE WHEN a.task_place IS NULL THEN '-' ELSE a.task_place END task_place,
					a.task_prop
				FROM
					t_common_task a INNER JOIN t_term at ON at.term_id = a.term_id INNER JOIN t_course ac ON ac.cid = a.cid
					INNER JOIN t_teacher atch ON a.tch_id = atch.tch_id INNER JOIN t_classroom_type act ON a.cr_type = act.cr_type
					INNER JOIN t_campus acu ON acu.campus_id = a.campus_id
				WHERE
						at.term_id = :termId 
					AND
						ac.institute_id = :depId 
					AND
						EXISTS(
							SELECT 1 FROM t_task_arrange b WHERE b.task_id = a.tsk_id AND b.term_id = :termId 
						)
				ORDER BY
					ac.course_name,a.cidx";
		
		queryObj = new Query(datasource = application.dnsSlave);
		queryObj.addParam(name = "depId", value = depId, cfsqltype = "cf_sql_varchar");
		queryObj.addParam(name = "termId", value = termId, cfsqltype = "cf_sql_char");
		rs_commonTask = queryObj.execute(sql = sql).getResult();

		
		sql = "	SELECT
					at.academic_year,at.academic_term,
					ac.course_name,a.cidx,
					atch.tch_name,atch.tch_id,
					a.task_limite,a.task_chooesd,act.cr_type_name,acu.campus_name,
					a.task_week,a.task_period,
					CASE WHEN a.task_time IS NULL THEN '-' ELSE a.task_time END task_time,
					CASE WHEN a.task_place IS NULL THEN '-' ELSE a.task_place END task_place,
					a.task_prop
				FROM
					t_reopen_task a INNER JOIN t_term at ON at.term_id = a.term_id INNER JOIN t_course ac ON ac.cid = a.cid
					INNER JOIN t_teacher atch ON a.tch_id = atch.tch_id INNER JOIN t_classroom_type act ON a.cr_type = act.cr_type
					INNER JOIN t_campus acu ON acu.campus_id = a.campus_id
				WHERE
						at.term_id = :termId 
					AND
						ac.institute_id = :depId 
					AND
						EXISTS(
							SELECT 1 FROM t_task_arrange b WHERE b.task_id = a.tsk_id AND b.term_id = :termId 
						)
				ORDER BY
					ac.course_name,a.cidx";
		
		queryObj = new Query(datasource = application.dnsSlave);
		queryObj.addParam(name = "depId", value = depId, cfsqltype = "cf_sql_varchar");
		queryObj.addParam(name = "termId", value = termId, cfsqltype = "cf_sql_char");
		rs_openTask = queryObj.execute(sql = sql).getResult();


		/* 载入相关操作组件 */
		excel = getProperty("serviceFactory").getBean("spreadSheetObject");
		mathAdvice = getProperty("serviceFactory").getBean("senateMathAdvice");
		
		/* 创建工作簿 */
		spreadsheetObj = excel.SpreadSheetNew("教学任务", false);
		
		/* 添加表头 */
		excel.SpreadsheetAddrow(spreadsheetObj, "课程类别,学年,学期,课程,课序号,教师,职工号,计划人数,实际人数,教室类别,排课校区,周次,周学时,上课时间,上课地点,周一,周二,周三,周四,周五,周六,周日,上课班级");
		
		row = 1;
		
		/* 写入计划任务 */
		for(a = 1; a lte rs_planTask.recordCount; a++){
			
			row++;
			
			rowData = "计划课程," & rs_planTask['academic_year'][a] & "," & rs_planTask['academic_term'][a] & "," & rs_planTask['course_name'][a] & "," & rs_planTask['cidx'][a] & "," & rs_planTask['tch_name'][a] & "," & rs_planTask['tch_id'][a] & "," & rs_planTask['task_limite'][a] & "," & rs_planTask['task_chooesd'][a] & "," & rs_planTask['cr_type_name'][a] & "," & rs_planTask['campus_name'][a] & "," & rs_planTask['task_week'][a] & "," & rs_planTask['task_period'][a]
						& "," & replace(mathAdvice.convertTaskTime(rs_planTask['task_time'][a]), ",", "、", "all") 
						& "," & replace(rs_planTask['task_place'][a], ",", "、", "all")
						& ", , , , , , , "
						& "," & replace(rs_planTask['task_classes'][a], ",", "，", "all");
			
			excel.SpreadsheetAddrow(spreadsheetObj, rowData);
			
			if (rs_planTask['task_time'][a] neq "-") {
				
				for (i=1; i lte listLen(rs_planTask['task_time'][a], ","); i++) {
					
					timeCell = listGetAt(rs_planTask['task_time'][a], i, ",");

					weekDay = listGetAt(timeCell, 1, "@-");
					lectureId = listGetAt(timeCell, 2, "@-");
					cells = listGetAt(timeCell, 3, "@-");
					
					column = 15 + weekDay;
					
					/* 显示当天上课时间 */
					cellValue = mathAdvice.convertLectureIdxToChinese(lectureId) & "(" & cells & "节)";
					
					if (rs_planTask['task_place'][a] neq "-" and listLen(rs_planTask['task_place'][a], ",") gte i) {
						
						/* 显示当天上课地点 */
						placeCell = listGetAt(rs_planTask['task_place'][a], i, ",");
						
						cellValue = cellValue & " " & placeCell;
						
					}
					
					excel.SpreadsheetSetCellValue(spreadsheetObj, cellValue, row, column);
					
				}
				
			}
			
		}
		
		
		/* 写入通选课任务 */
		for(a = 1; a lte rs_commonTask.recordCount; a++){
			
			row++;
			
			rowData = "全校通选课," & rs_commonTask['academic_year'][a] & "," & rs_commonTask['academic_term'][a] & "," & rs_commonTask['course_name'][a] & "," & rs_commonTask['cidx'][a] & "," & rs_commonTask['tch_name'][a] & "," & rs_commonTask['tch_id'][a] & "," & rs_commonTask['task_limite'][a] & "," & rs_commonTask['task_chooesd'][a] & "," & rs_commonTask['cr_type_name'][a] & "," & rs_commonTask['campus_name'][a] & "," & rs_commonTask['task_week'][a] & "," & rs_commonTask['task_period'][a]
						& "," & replace(mathAdvice.convertTaskTime(rs_commonTask['task_time'][a]), ",", "、", "all") 
						& "," & replace(rs_commonTask['task_place'][a], ",", "、", "all")
						& ", , , , , , , , , ,";
			
			excel.SpreadsheetAddrow(spreadsheetObj, rowData);
			
			if (rs_commonTask['task_time'][a] neq "-") {
				
				for (i=1; i lte listLen(rs_commonTask['task_time'][a], ","); i++) {
					
					timeCell = listGetAt(rs_commonTask['task_time'][a], i, ",");

					weekDay = listGetAt(timeCell, 1, "@-");
					lectureId = listGetAt(timeCell, 2, "@-");
					cells = listGetAt(timeCell, 3, "@-");
					
					column = 15 + weekDay;
					
					/* 显示当天上课时间 */
					cellValue = mathAdvice.convertLectureIdxToChinese(lectureId) & "(" & cells & "节)";
					
					if (rs_commonTask['task_place'][a] neq "-" and listLen(rs_commonTask['task_place'][a], ",") gte i) {
						
						/* 显示当天上课地点 */
						placeCell = listGetAt(rs_commonTask['task_place'][a], i, ",");
						
						cellValue = cellValue & " " & placeCell;
						
					}
					
					excel.SpreadsheetSetCellValue(spreadsheetObj, cellValue, row, column);
					
				}
				
			}
			
		}
		
		
		/* 写入单开班任务 */
		for(a = 1; a lte rs_openTask.recordCount; a++){
			
			row++;
			
			rowData = "单开班课程," & rs_openTask['academic_year'][a] & "," & rs_openTask['academic_term'][a] & "," & rs_openTask['course_name'][a] & "," & rs_openTask['cidx'][a] & "," & rs_openTask['tch_name'][a] & "," & rs_openTask['tch_id'][a] & "," & rs_openTask['task_limite'][a] & "," & rs_openTask['task_chooesd'][a] & "," & rs_openTask['cr_type_name'][a] & "," & rs_openTask['campus_name'][a] & "," & rs_openTask['task_week'][a] & "," & rs_openTask['task_period'][a]
						& "," & replace(mathAdvice.convertTaskTime(rs_openTask['task_time'][a]), ",", "、", "all") 
						& "," & replace(rs_openTask['task_place'][a], ",", "、", "all")
						& ", , , , , , , , , ,";
			
			excel.SpreadsheetAddrow(spreadsheetObj, rowData);
			
			if (rs_openTask['task_time'][a] neq "-") {
				
				for (i=1; i lte listLen(rs_openTask['task_time'][a], ","); i++) {
					
					timeCell = listGetAt(rs_openTask['task_time'][a], i, ",");

					weekDay = listGetAt(timeCell, 1, "@-");
					lectureId = listGetAt(timeCell, 2, "@-");
					cells = listGetAt(timeCell, 3, "@-");
					
					column = 15 + weekDay;
					
					/* 显示当天上课时间 */
					cellValue = mathAdvice.convertLectureIdxToChinese(lectureId) & "(" & cells & "节)";
					
					if (rs_openTask['task_place'][a] neq "-" and listLen(rs_openTask['task_place'][a], ",") gte i) {
						
						/* 显示当天上课地点 */
						placeCell = listGetAt(rs_openTask['task_place'][a], i, ",");
						
						cellValue = cellValue & " " & placeCell;
						
					}
					
					excel.SpreadsheetSetCellValue(spreadsheetObj, cellValue, row, column);
					
				}
				
			}
			
		}
		
		excel.SpreadSheetWrite(spreadsheetObj, temFile, true);
		
	</cfscript>
	<cfheader name="Content-Disposition" value="attachment; filename=#downFile#.xls" />
	<cfcontent file="#temFile#" reset="yes" type="application/msexcel" deletefile="yes" />
</cfsilent>